package cn.kgcnews.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.kgcnews.dao.UserDao;
import cn.kgcnews.pojo.NewsDetail;
import cn.kgcnews.pojo.User;
import cn.kgcnews.utils.BaseDao;

public class UserDaoImpl extends BaseDao implements UserDao {

	@Override
	public int save(User user) throws Exception {
		String sql="INSERT INTO news_users(username,userpass,usertype,email) VALUES(?,?,?,?)";
		Object[] params={user.getUserName(),user.getUserPass(),user.getType(),user.getEmail()};
		
		return super.executeUpdate(sql, params);
	}

	@Override
	public User queryByUserName(String userName) throws Exception {
		User user=null;
		String sql="SELECT uid,username,userpass,usertype,email FROM news_users WHERE username=?";
		Object[] params={userName};
		ResultSet rs=super.executeQuery(sql, params);
		if(rs.next()){
			user=new User();
			user.setId(rs.getInt("uid"));
			user.setUserName(rs.getString("username"));
			user.setUserPass(rs.getString("userpass"));
			user.setType(rs.getInt("usertype"));
			user.setEmail(rs.getString("email"));
		}
		super.closeResource(rs);
		return user;
	}

	@Override
	public List<User> queryAllUser() throws Exception {
		
		List<User> list=new ArrayList<User>();
		String sql="SELECT uid,username,userpass,usertype,email FROM news_users ORDER BY uid DESC";
		
		ResultSet rs=super.executeQuery(sql, null);
		while(rs!=null && rs.next()){
			User user=new User();
			user.setId(rs.getInt("uid"));
			user.setUserName(rs.getString("username"));
			user.setUserPass(rs.getString("userpass"));
			user.setType(rs.getInt("usertype"));
			user.setEmail(rs.getString("email"));
			list.add(user);
		}
		super.closeResource(rs);
		return list;
	}

	@Override
	public int queryTotalRecord(String username, int roleId) throws Exception {
		List paramsList=new ArrayList();
		int row=0;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT COUNT(1) FROM news_users");
		sql.append(" where 1=1 ");
		if(username!=null && !"".equals(username)){
			sql.append(" and username like ?");
			paramsList.add(username);
		}
		if(roleId>=0){
			sql.append(" and usertype=?");
			paramsList.add(roleId);
		}
		Object[] params=paramsList.toArray();
		ResultSet rs=super.executeQuery(sql.toString(), params);
		if(rs!=null && rs.next()){
			row=rs.getInt(1);
		}
		super.closeResource(rs);
		return row;
		
	}

	@Override
	public List<User> queryUserByOffset(int offSet, int pageSize,
			String username, int roleId) throws Exception {
		
		List<User> list = new ArrayList<User>();
		List paramsList=new ArrayList();
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT uid,username, userpass,usertype,email");
		sql.append(" FROM news_users where 1=1 ");
		if(username!=null && !"".equals(username)){
			sql.append(" and username like ?");
			paramsList.add(username);
		}
		if(roleId>=0){
			sql.append(" and usertype=?");
			paramsList.add(roleId);
		}
		sql.append(" ORDER BY uid DESC LIMIT ?,? ");
		paramsList.add(offSet);
		paramsList.add(pageSize);
		/*System.out.println("发送的sql:"+sql);
		System.out.println(roleId);
		System.out.println(offSet);
		System.out.println(username);*/
		Object[] params=paramsList.toArray();
		ResultSet rs = super.executeQuery(sql.toString(), params);
		while (rs != null && rs.next()) {
			User user = new User();
			user.setId(rs.getInt("uid"));
			user.setUserName(rs.getString("username"));
			user.setUserPass(rs.getString("userpass"));
			user.setType(rs.getInt("usertype"));
			user.setEmail(rs.getString("email"));
			list.add(user);
		}
		super.closeResource(rs);
		System.out.println("集合中的数据条数为："+list.size());
		
		return list;
		
		
	}

	@Override
	public User findUserByUserId(int userid) throws Exception{
		String sql="SELECT uid,username, userpass,usertype,email FROM news_users where uid=?";
		Object[] params={userid};
		ResultSet rs = super.executeQuery(sql, params);
		if(rs!=null&&rs.next()){
			
		}
		return null;
	}

	@Override
	public List<User> queryByUserEmail(String userEmail) throws Exception {
		List<User> list=new ArrayList<User>();
		User user=null;
		String sql="SELECT uid,username,userpass,usertype,email FROM news_users WHERE email=?";
		Object[] params={userEmail};
		ResultSet rs=super.executeQuery(sql, params);
		if(rs!=null&&rs.next()){
		while(rs.next()){
			user=new User();
			user.setId(rs.getInt("uid"));
			user.setUserName(rs.getString("username"));
			user.setUserPass(rs.getString("userpass"));
			user.setType(rs.getInt("usertype"));
			user.setEmail(rs.getString("email"));
			list.add(user);
		}
		}
		super.closeResource(rs);
		return list;
	}

}
